﻿// -----------------------------------------------------------------------
// <copyright file="SqlServerDatabase.cs" company="Acadian Asset Management, LLC.">
// TODO: Update copyright text.
// </copyright>
// -----------------------------------------------------------------------

namespace eBenefits.Data
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Configuration;

    /// <summary>
    /// TODO: Update summary.
    /// </summary>
    // Defines all the functionality that we will need to read data from database
    public sealed class SqlServerDatabase
    {
        private string connectionString { get; set; }

        public SqlServerDatabase(string connectionStringName)
        {
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[connectionStringName];

            if (settings == null)
                throw new Exception("Connection string not found");

            this.connectionString = settings.ConnectionString;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">Name of stored procedure</param>
        /// <returns></returns>
        public DataSet FetchDataSet(string commandText)
        {
            return FetchDataSet(commandText, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">Name of stored procedure</param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet FetchDataSet(string commandText, params SqlParameter[] parameters)
        {
            //SqlParameterCollection parametersCollection = new SqlParameterCollection();
            //foreach (SqlParameter p in parameters)
            //{
            //    parametersCollection.Add(p);
            //}

            return FetchDataSet(commandText, CommandType.StoredProcedure, parameters);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">Dependent on commandType; Could be SQL statement, name of stored procedure or table name</param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public DataSet FetchDataSet(string commandText, CommandType commandType)
        {
            #region Idea to log stack

            // write call stack method names
            //System.Diagnostics.StackTrace stackTrace = new System.Diagnostics.StackTrace();           // get call stack
            //System.Diagnostics.StackFrame[] stackFrames = stackTrace.GetFrames();  // get method calls (frames)
            //foreach (System.Diagnostics.StackFrame stackFrame in stackFrames)
            //{
            //    Console.WriteLine(stackFrame.GetMethod().Name);   // write method name
            //}
            //stackFrames[1].GetMethod().Name
            #endregion Idea to log stack

            DataSet ds = new DataSet();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = commandType;
                cmd.CommandText = commandText;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                //SaveDataAsXml(commandText, ds);

                return ds;
            }
        }

        public DataSet FetchDataSet(string commandText, CommandType commandType, SqlParameter[] parameters)
        {
            DataSet ds = new DataSet();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = commandType;
                cmd.CommandText = commandText;
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                //SaveDataAsXml(commandText, parameters, ds);

                return ds;
            }
        }

        public DataTable FetchSchema(string tableName)
        {
            DataSet ds = new DataSet();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("SELECT * FROM {0} WHERE 1 = 0", tableName);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.FillSchema(ds, SchemaType.Mapped);

                if ((ds != null) && (ds.Tables.Count > 0))
                {
                    ds.Tables[0].TableName = tableName;
                    return ds.Tables[0];
                }
            }

            return null;
        }

        #region Helper functions to save retrieved data as xml (TODO)
        //private void SaveDataAsXml(string commandText, DataSet ds)
        //{
        //    if ((ds == null) || (ds.Tables.Count <= 0))
        //        return;

        //    string directoryPath = HttpContext.Current.Server.MapPath("~/App_Data/Site_Data/ResearchPortal/Xml");
        //    string hashCode = HashHelper.MD5Hash(commandText);
        //    string filePath = Path.Combine(directoryPath, hashCode);

        //    filePath = Path.ChangeExtension(filePath, "xml");
        //    if (!File.Exists(filePath))
        //        ds.WriteXml(filePath, XmlWriteMode.WriteSchema);
        //    else
        //        ds.WriteXml(filePath, XmlWriteMode.WriteSchema);
        //}

        //private void SaveDataAsXml(string commandText, SqlParameter[] parameters, DataSet ds)
        //{
        //    foreach (SqlParameter p in parameters)
        //    {
        //        commandText = commandText + string.Format(" {0}={1}", p.ParameterName, p.Value.ToString());
        //    }

        //    SaveDataAsXml(commandText, ds);
        //}
        #endregion Helper functions to save retrieved data as xml

    }
}
